---
title: "HTML widgets in Power BI"
author: "Stéphane Laurent"
date: '2022-11-24'
tags: R, misc
rbloggers: yes
output:
  md_document:
    variant: markdown
    preserve_yaml: true
  html_document:
    highlight: kate
    keep_md: no
highlighter: pandoc-solarized
---

- [Setting up an environment](#setting-up-an-environment)

- [Example](#example)

- [Notes](#notes)

- [Advanced example](#advanced-example)

- [Arbitrary HTML](#arbitrary-html)

- [Using multiple datasets](#using-multiple-datasets)

- [Limiting the number of selected columns](#limiting-the-number-of-selected-columns)

- [Combining widgets](#combining-widgets)

- [Prevent removal of duplicated rows](#prevent-removal-of-duplicated-rows)

- [Adding formatting objects](#adding-formatting-objects)

- [Restricting a number input to a range](#restricting-a-number-input-to-a-range)


___

It is possible to include a `htmlwidget` in a **Power BI** report, such as a `plotly` graphic.

# Setting up an environment

You first have to follow the first three steps of [this tutorial](https://docs.microsoft.com/en-us/power-bi/developer/visuals/environment-setup?tabs=windows):

1. install **Node.js**;

2. install **pbiviz**;

3. create and install a certificate;

4. install the npm package `powerbi-visuals-api`.

For Windows, you can download a portable version of **Node.js** on the [nodejs.org](https://nodejs.org/en/download/) website: choose the 64-bit zip, unzip it in a folder and set this folder in your Windows path. 

Follow the tutorial for steps 2 and 3. Note that you have to perform step 3 only once, there's no need to generate a new certificate for each visual you will create.

For step 4, run `npm install -g powerbi-visuals-api` in a terminal.


# Example

Let's assume you want to render the interactive graphic produced by this code:

```r
library(plotly)
gg <- ggplot(iris, aes(x = Petal.Length, y = Sepal.Length, color = Species)) + 
  geom_point()
ggplotly(gg)  
```

- create a working directory: `mkdir pbiwidgets` and go into it: `cd pbiwidgets`;

- initialize a new visual by running: `pbiviz new iris -t rhtml`;

- this creates the folder ***pbiwidgets/iris*** with a lot of stuff;

- in this folder, edit the file ***pbiviz.json***; you have to write something for `description` (what you want), `supportUrl`(write anything you want, e.g. `https://www.example.com`), and you have to write something for the fields `name` and `email`;

- now you have to edit ***script.r***:

```r
source('./r_files/flatten_HTML.r')

############### Library Declarations ###############
libraryRequireInstall("plotly")
####################################################

################### Actual code ####################
gg <- ggplot(Values, aes(x=Petal.Length, y=Sepal.Length, color = Species)) + 
  geom_point()
p <- ggplotly(gg)
####################################################

################## Save widget #####################
internalSaveWidget(p, "out.html")
####################################################

################ Reduce paddings ###################
ReadFullFileReplaceString(
  "out.html", "out.html", ',"padding":[0-9]*,', ',"padding":0,'
)
####################################################
```

- the dataframe `Values` is the dataset which will be sent to this R script by **Power BI**; 

- since we want to use `iris` as data, we save it to an Excel file that we will import in **Power BI**; I recommend to use **writexl** (the Excel files written with **openxlsx** cause an issue when one tries to load them in **Power BI**):

```
writexl::write_xlsx(iris, "iris.xlsx") 
# or writexl::write_xlsx(list("sheetName" = iris), "iris.xlsx") 
```

- go to the folder ***pbiwidgets/iris*** in your terminal and run the command `pbiviz package`;

- now open Power BI, import ***iris.xlsx***, select and load the sheet;

- in the *'Visualizations'* panel, click the three dots, then *'import a visual from a file'*, and select the ***pbiviz*** file which is located in ***pbiwidgets/iris/dist***

- a new [icon showing some tools](https://laustep.github.io/stlahblog/posts/figures/pbiviz_icon.png) appears at the bottom in the *'Visualizations'* panel, click it;

- in the *'Fields'* panel, select the columns used for the plot, here `Petal.Length`, `Sepal.Length` and `Species`;

- that's it, you get the interactive graphic in Power BI.

## Helper function

If you have to deal with multiple widgets, you will be bothered about the filling of the ***pbiviz.json*** file. Here is a R function which fills it for you:

```r
library(jsonlite)
fill_pbiviz <- function(path){
  wd <- setwd(path)
  on.exit(setwd(wd))
  pbiviz <- fromJSON("pbiviz.json")
  pbiviz[["visual"]][["description"]] <- "Description"
  pbiviz[["visual"]][["supportUrl"]] <- "https://wwww.example.com"
  pbiviz[["author"]][["name"]] <- "John Doe"
  pbiviz[["author"]][["email"]] <- "john.doe@gmail.com"
  pbiviz <- toJSON(pbiviz, auto_unbox = TRUE, null = "null", pretty = 2)
  writeLines(pbiviz, "pbiviz.json")
}
``` 

Just run `fill_pbiviz("path/to/pbiwidgets/iris")`.


# Notes

This works very well with `plotly` and `ggplotly` widgets. I also tested a graphic with the `scatterD3` package and the result was fine as well. But this can be less fine for other `htmlwidgets`. 

For example, with the `ggiraph` package, I managed to achieve a good result after settings the options `width_svg = 3, height_svg = 2.5` in the `girafe` function. I also set the option `opts_zoom(min = .7, max = 2)` in the `girafe_options` function:

```r
gg_point <- ggplot(data = data) +
  geom_point_interactive(......
p <- girafe(ggobj = gg_point, width_svg = 3, height_svg = 2.5)
p <- girafe_options(p, opts_zoom(min = .7, max = 2))
internalSaveWidget(p, "out.html")
```

I tried a pie chart with the `rAmCharts4` package and got an empty plot. I finally managed to get the pie chart by removing the last lines of the file ***script.r***:

```r
################ Reduce paddings ###################
ReadFullFileReplaceString(
  "out.html", "out.html", ',"padding":[0-9]*,', ',"padding":0,'
)
```

And I set the `height` option of the `amPieChart` function to `300px`, otherwise the legend was truncated.


# Advanced example

When you select the columns `Petal.Length`, `Sepal.Length` and `Species`, they appear in the *'Visualizations'* panel, under the dataset name **Values**:

![](./figures/pbiviz01.png)

Then, **Power BI** sends the data to the R script with the help of a CSV file, and *the columns of this CSV file are in the same order as your selected columns*.

Therefore, instead of using the column names in the R script, one can use the column indices.

Let's illustrate. We will create a scatter plot as in the previous example, but the user will have the choice of the variables on the x-axis and the y-axis, and he'll can also choose or not a color factor. 

Firstly, we change the display name of the **Values** dataset. Open the file `pbiwidgets/iris/capabiities.json` (with any text editor), and change the display name in the `dataRoles` field:

```json
{
	"dataRoles" : [
		{
			"displayName" : "Data (x, y, by)",
			"kind" : "GroupingOrMeasure",
			"name" : "Values"
		}
	],
        ......
``` 

Now we will see **Data (x, y, by)** in the *'Visualizations'* panel, instead of **Values**:

![](./figures/pbiviz02.png)

Then we use the following R code in ***script.r***:

```r
################### Actual code ####################
nms <- colnames(Values)
ncols <- length(nms)
if(ncols == 2L){
  gg <- ggplot(
    Values, aes(x=.data[[nms[1]]], y=.data[[nms[2]]])
  ) + 
    geom_point()
}else if(ncols >= 3L){
  gg <- ggplot(
    Values, aes(x=.data[[nms[1]]], y=.data[[nms[2]]], color = .data[[nms[3]]])
  ) + 
    geom_point()
}else{
  gg <- ggplot() + geom_blank()
}
p <- ggplotly(gg)
####################################################
```

Now we can choose the columns we want for the x-axis and for the y-axis, and we can optionally choose the `Species` color factor:

![](./figures/pbiviz03.png){width="50%"}

![](./figures/pbiviz04.png){width="50%"}


# Arbitrary HTML

In fact, **pbiviz** is not restricted to render a `htmlwidget`. One can render any HTML file, it suffices to save it under the name ***out.html*** in the R script. For instance, in the above example, we render a blank plot if the user does not select at least two columns. We can throw a message instead, with a R script like this:

```r
source("./r_files/flatten_HTML.r")

############### Library Declarations ###############
libraryRequireInstall("plotly")
####################################################

htmlMessage <- function(message){
  html <- c(
    "<!doctype html>",
    "<html lang=en>",
    "  <head>",
    "    <meta charset=utf-8>",
    "  </head>",
    "  <body>",
    sprintf("    <p>%s</p>", message),
    "  </body>",
    "</html>"
  )
  writeLines(html, "out.html")
}

################### Actual code ####################
nms <- colnames(Values)
ncols <- length(nms)
if(ncols == 2L){
  gg <- ggplot(
    Values, aes(x=.data[[nms[1]]], y=.data[[nms[2]]])
  ) + 
    geom_point()
  p <- ggplotly(gg) 
  internalSaveWidget(p, "out.html")
}else if(ncols >= 3L){
  gg <- ggplot(
    Values, aes(x=.data[[nms[1]]], y=.data[[nms[2]]], color = .data[[nms[3]]])
  ) + 
    geom_point()
  p <- ggplotly(gg) 
  internalSaveWidget(p, "out.html")
}else{
  htmlMessage(
    "You have to select at least two columns."
  )
}
################## End of script ###################
```


# Using multiple datasets

Some graphics require more than one dataset. We give an example here. 

First, we prepare two datasets:

```r
library(dplyr)
data("diamonds", package = "ggplot2")

## creating dataset #1
data1 <- 
  diamonds %>% 
  group_by(clarity) %>% 
  summarize(m = mean(price))  

## creating dataset #2
data2 <- 
  diamonds %>% 
  group_by(clarity, cut) %>% 
  summarize(m = mean(price))

## save to Excel files
writexl::write_xlsx(list("data1" = data1), "diamonds_summarized1.xlsx")
writexl::write_xlsx(list("data2" = data2), "diamonds_summarized2.xlsx")
```

Create the visual project: `pbiviz new diamonds -t rhtml`. Complete the file ***pbiwidgets/diamonds/pbiviz.json*** as previously explained, and modify the file ***pbiwidgets/diamonds/capabilities.json*** as follows:

```json
{
  "dataRoles": [
    {
      "displayName": "Data1",
      "kind": "GroupingOrMeasure",
      "name": "data1"
    },
    {
      "displayName": "Data2",
      "kind": "GroupingOrMeasure",
      "name": "data2"
    }
  ],
  "dataViewMappings": [
    {
      "scriptResult": {
        "dataInput": {
          "table": {
            "rows": {
              "select": [
                {
                  "for": {
                    "in": "data1"
                  }
                },
                {
                  "for": {
                    "in": "data2"
                  }
                }
              ],
              ......
```

Now the R script, in ***pbiwidgets/diamonds/script.r***:

```r
source('./r_files/flatten_HTML.r')

############### Library Declarations ###############
libraryRequireInstall("plotly")
####################################################

################### Actual code ####################
gg <- ggplot() +
  geom_point(data = data1, aes(x = clarity, y = m), color = "blue", size = 3) + 
  geom_point(data = data2, aes(x = clarity, y = m), color = "red")
####################################################

############# Create and save widget ###############
p <- ggplotly(gg)
internalSaveWidget(p, 'out.html')
####################################################
```

Now you can compile the project: run `pbiviz package` from the folder ***pbiwidgets/diamonds***.

Open **Power BI** and load the two Excel files. Select *'Get more visuals'* as before (the icon with the three dots), *'Import a visual from a file'* and select the **pbiviz** file located in the ***pbiwidgets/diamonds/dist*** folder.

Drag and drop the column names of the two datasets to put them in **Data1** and **Data2**. Here is the result:

![](./figures/pbiviz05.png){width="50%"}

## Helper function

Here is a R function which generates the ***capabilities.json*** file:

```r
library(jsonlite)
fill_capabilities <- function(path, dataNames, dataDisplayNames){
  wd <- setwd(path)
  on.exit(setwd(wd))
  caps <- fromJSON("capabilities.json", simplifyVector = FALSE)
  caps[["dataRoles"]] <- mapply(function(name, displayName){
    list(
      "displayName" = displayName, 
      "kind"        = "GroupingOrMeasure", 
      "name"        = name
    )
  }, dataNames, dataDisplayNames, SIMPLIFY = FALSE, USE.NAMES = FALSE)
  caps[["dataViewMappings"]][[
    1L
  ]][["scriptResult"]][["dataInput"]][["table"]][["rows"]][["select"]] <-
    lapply(dataNames, function(dataName){
      list("for" = list("in" = dataName))
    })
  caps <- toJSON(caps, auto_unbox = TRUE, null = "null", pretty = 2)
  writeLines(caps, "capabilities.json")
}
```

Usage example: 

```r
fill_capabilities(
  "path/to/pbiwidgets/diamonds", c("data1", "data2"), c("Data1", "Data2")
)
```


# Limiting the number of selected columns

We can limit the number of columns that the user can select in a table. For instance, let's take the previous example. The maximum number of columns is set in the file ***capabilities.json***, in the `dataViewMappings` field:

```json
{
  "dataRoles": [
    {
      "displayName": "Data1",
      "kind": "GroupingOrMeasure",
      "name": "data1"
    },
    {
      "displayName": "Data2",
      "kind": "GroupingOrMeasure",
      "name": "data2"
    }
  ],
  "dataViewMappings": [
    {
      "conditions": [
        {
          "data1": {
            "max": 2
          },
          "data2": {
            "max": 2
          }
        }
      ],
      "scriptResult": {
        ......
```


# Combining widgets

The [manipulateWidget package](https://cran.r-project.org/web/packages/manipulateWidget/vignettes/manipulateWidgets.html) provides a useful function `combineWidgets` which, as its name indicates, allows to combine several widgets. 

Here is an example using the widgets of the **dygraphs** package.

```r
source('./r_files/flatten_HTML.r')

############### Library Declarations ###############
libraryRequireInstall("dygraphs")
libraryRequireInstall("manipulateWidget")
####################################################

################### Actual code ####################
y1 <- Values[[1]]
y2 <- Values[[2]]
x <- 1L:nrow(Values)
columnNames <- colnames(Values)
df1 <- data.frame(x = x, y = y1)
df2 <- data.frame(x = x, y = y2)
widget1 <- dygraph(df1, main = columnNames[1L])
widget2 <- dygraph(df2, main = columnNames[2L])
####################################################

############# Create and save widget ###############
widget <- combineWidgets(widget1, widget2)
internalSaveWidget(widget, 'out.html')
####################################################
```

![](./figures/pbiviz06.png){width="50%"}


# Prevent removal of duplicated rows

Power BI has an undesirable behavior: when sending the data to R, it removes the duplicated rows. To alleviate this problem, you have to add a column without duplicated values. An easy way to get such a column consists in going to the Power Query Editor (*"Transform data"* button), then going to the tab *"Add column"* and select *"Index Column"*. Then include this column in the data exported to R.


# Adding formatting objects

Here we explain how to achieve the result shown on the image below. With this visual, the user is able to add a title to the chart and to select a theme for the chart.

![](./figures/pbiviz07.gif){width="50%"}

This is achieved by modifying two files: the file ***capabilities.json*** and the file ***src/settings.ts*** in the folder created by **pbiviz**.

Here is the file ***capabilities.json***. We modified the `objects` node:

```json
{
  "dataRoles": [
    {
      "displayName": "Data (x, y, color, index)",
      "kind": "GroupingOrMeasure",
      "name": "dat"
    }
  ],
  "objects": {
    "ggsettings": {
      "displayName": "Chart Settings",
      "description": "Chart theme and title",
      "properties": {
        "ChartTitle": {
          "displayName": "Chart Title",
          "description": "Set chart title",
          "type": {
            "text": true
          }
        },
        "ChartTheme": {
          "displayName": "Chart Theme",
          "description": "Select the Chart Theme",
          "type": {
            "enumeration": [
              {
                "displayName": "Classic",
                "value": "classic"
              },
              {
                "displayName": "Economist",
                "value": "economist"
              },
              {
                "displayName": "Excel",
                "value": "excel"
              }
            ]
          }
        }
      }
    }
  },
  "dataViewMappings": [
    {
     ......
```

And here is the TypeScript file ***src/settings.ts***:

```ts
"use strict";

import { dataViewObjectsParser } from "powerbi-visuals-utils-dataviewutils";
import DataViewObjectsParser = dataViewObjectsParser.DataViewObjectsParser;

export class VisualSettings extends DataViewObjectsParser {
  public ggsettings: ChartSettings = new ChartSettings();
}

export class ChartSettings {
  public ChartTitle: string = "";
  public ChartTheme: string = "classic";
}
```

Then, in the file ***script.r*** the title set by the user and the theme he chooses are named `ggsettings_ChartTitle` and `ggsettings_ChartTheme` respectively. But they won't be available at the first creation of the chart, so we have to check for their existence in the R script:

```r
if(exists("ggsettings_ChartTheme") && exists("ggsettings_ChartTitle")){
  ......
```

Here is the full script I used:

```r
source('./r_files/flatten_HTML.r')

############### Library Declarations ###############
libraryRequireInstall("plotly")
library(ggthemes)
####################################################

################### Actual code ####################
nms <- colnames(dat)
ncols <- length(nms)
if(ncols == 4){
  gg <- ggplot(
    dat, aes_string(x = nms[1], y = nms[2], color = nms[3])
  ) + 
    geom_point()
}else{
  stop("You have to select four columns!")
}

if(exists("ggsettings_ChartTheme") && exists("ggsettings_ChartTitle")){
  gg <- gg + ggtitle(ggsettings_ChartTitle)
  if(ggsettings_ChartTheme == "economist"){
    gg <- gg + scale_color_economist() + theme_economist()
  }else if(ggsettings_ChartTheme == "excel"){
    gg <- gg + scale_color_excel_new() + theme_excel_new()
  }
}
####################################################

############# Create and save widget ###############
p <- ggplotly(gg)
internalSaveWidget(p, 'out.html')
####################################################
``` 

## List of available object types

- color picker: 

```json
"type": {
  "fill": {
    "solid": {
      "color": true
    }
  }
}
```

- text input: 

```json
"type": {
  "text": true
}
```

- number input: 

```json
"type": {
  "numeric": true
}
```

- integer input: 

```json
"type": {
  "integer": true
}
```

- switch input (true/false): 

```json
"type": {
  "bool": true
}
```

- percentage input: 

```json
"type": {
  "formatting": {
    "fontSize": true
  }
}
```

- dropdown list: 

```json
"type": {
  "enumeration": [
    {
      "displayName": "Item 1",
      "value": "item1"
    },
    {
      "displayName": "Item 2",
      "value": "item2"
    }
  ]
}
```


# Restricting a number input to a range

It might be desirable to have a minimum value and a maximum value for a number input. Assume you have such an `objects` field in ***capabilities.json***:

```json
  "objects": {
    "regressionline": {
      "displayName": "Regression Line",
      "description": "Add regression line",
      "properties": {
        "AddLine": {
          "displayName": "Add",
          "description": "Add regression line",
          "type": {
            "bool": true
          }
        },
        "LineWidth"; {
          "displayName": "Line Width",
          "description": "Width of regression line",
          "type": {
            "numeric": true
          }
        }
      }
    },
    ......
  }
```

You want to restrict the choice of the line width, say for example it must lie between 2 and 8. Then you have to modify the file ***src/visual.ts***. Firstly, you have to add this import:

```ts
import VisualObjectInstanceEnumeration = powerbi.VisualObjectInstanceEnumeration;
```

(add it in the block of imports at the beginning of the file). Secondly, you have to replace the function `enumerateObjectInstances` defined at the end with:

```ts
public enumerateObjectInstances(
  options: EnumerateVisualObjectInstancesOptions
): VisualObjectInstanceEnumeration {
  var enumeratedObjects: VisualObjectInstanceEnumerationObject = <
    VisualObjectInstanceEnumerationObject
  >VisualSettings.enumerateObjectInstances(
    this.settings || VisualSettings.getDefault(), options
  );

  if (options.objectName === "regressionline") {
    enumeratedObjects.instances[0].validValues = {
      LineWidth: { numberRange: { min: 2, max: 8 } }
    };
  }

  return enumeratedObjects;
}
```